/***
    Amrita ITEWS - Copyright (C) 2009 Amrita Vishwa Vidyapeetham, Amritapuri.
                                       (http://www.amrita.edu)
    ***************************************************************************
    This file is part of the Amrita ITEWS distribution.
    Amrita ITEWS is free software; you can redistribute it and/or modify it 
    under the terms of the GNU General Public License (version 2) as published
    by the Free Software Foundation AND MODIFIED BY the Amrita ITEWS exception.
    ***NOTE*** The exception to the GPL is included to allow you to distribute
    a combined work that includes Amrita ITEWS without being obliged to provide
    the source code for proprietary components outside of the Amrita ITEWS
    software. Amrita ITEWS is distributed in the hope that it will be useful, 
    but WITHOUT ANY WARRANTY; without even the implied warranty of 
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU General
    Public License for more details. You should have received a copy of the GNU
    General Public License and the Amrita ITEWS license exception along with 
    Amrita ITEWS if not then it can be viewed here: 
    http://itews.amrita.ac.in/license.html.

    Documentation, latest information, license and contact details are at:
    http://itews.amrita.ac.in/

    Amrita ITEWS source code can be found at:
    http://code.google.com/p/itews

    The initial code-base of Amrita ITEWS was developed by Amrita Vishwa 
    Vidyapeetham as part of the project titled,"Intelligent & Interactive 
    Telematics using Emerging Wireless Technologies for Transport Systems 
    (ITEWS)" sponsored by Technology Information Forecasting and Assessment 
    Council (TIFAC), India.
***/

package tdcsinfoservice;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.StringTokenizer;

import org.knopflerfish.service.log.LogRef;
import org.osgi.service.event.Event;

public class NodeInfoDatabaseUpdater
{
	private static final String CONTROL_PARAM = "control_param";
	private static final String NODE_LIST = "node_list";
	private static final String NODE_CODE = "node_code";
	private static final String NODE_LAT = "node_latitude";
	private static final String NODE_LONG = "node_longitude";
	private static final String NODE_AREA = "node_area";
	private static final String NODE_NAME_ = "node_name_";
	private static final String DELETE_OPT = "delete";
	private DBConnectionPool dbConnectionPool ;
	private TDCSInfoServiceConfig config;
	private  LogRef log;
	private String nodesQuery = "SELECT id from nodes where node_code=?";
	private String nodesInsertFull = "INSERT INTO nodes VALUES(default, ?, ?, ?, GeomFromText(?))";
	private String nodesUpdate = "UPDATE nodes SET node_lat=?, node_long=?, node_area=GeomFromText(?) WHERE node_code=?";
	private String nodeNamesInsert = "INSERT INTO node_names VALUES(default, ?, ?, ?)";
	private String nodeNamesQuery = "SELECT id FROM node_names WHERE node_code=? AND lang_code=?";
	private String nodeNamesUpdate = "UPDATE node_names SET node_name=? WHERE node_code=? AND lang_code=?";
	private String nodesDelete = "DELETE FROM nodes WHERE node_code=?";
	
	public NodeInfoDatabaseUpdater()
	{
		try
		{
			log = new LogRef(Activator.bc);
			config = TDCSInfoServiceConfig.getConfig();
			dbConnectionPool = DBConnectionPool.getInstance(config.getJdbcConnectionURI());
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
	}
	
	public synchronized void updateNodeInfo(Event event)
	{
		Connection conn = null;
		PreparedStatement preparedStmt1 = null;
		PreparedStatement preparedStmt2 = null;
		PreparedStatement preparedStmt3 = null;
		PreparedStatement preparedStmt4 = null;
		PreparedStatement preparedStmt5 = null;
		PreparedStatement preparedStmt5A = null;
		PreparedStatement preparedStmt6 = null;
		PreparedStatement preparedStmt6A = null;
		ResultSet rs1 = null;
		ResultSet rs2 = null;
		try 
		{
			Object nodeListObj = event.getProperty(NODE_LIST);
			if (nodeListObj == null || ((ArrayList) nodeListObj).size() == 0)
			{
				throw new Exception("Node Info database update aborted. Node List empty or not available in feed. TDCS should supply correct feed");
			}
			ArrayList nodeList = (ArrayList) nodeListObj;
			conn = dbConnectionPool.getConnection();
			conn.setAutoCommit(false);
			for (int i=0; i<nodeList.size(); i++)
			{
				String controlParam=null, nodeCode=null, nodeArea=null;
				double nodeLat = 999d, nodeLong=999d;
				Object attr=null;
				HashMap nodeMember = (HashMap) nodeList.get(i);
				attr = nodeMember.get(CONTROL_PARAM);
				if (attr != null) controlParam = (String) attr;
				attr = nodeMember.get(NODE_CODE);
				if (attr != null) nodeCode = (String) attr;
				attr = nodeMember.get(NODE_LAT);
				if (attr != null)
				{
					if (attr instanceof Double)
						nodeLat = (Double) attr;
					else if (attr instanceof Integer)
						nodeLat =  (Integer) attr;
				}
				attr = nodeMember.get(NODE_LONG);
				if (attr != null)
				{
					if (attr instanceof Double)
						nodeLong = (Double) attr;
					else if (attr instanceof Integer)
						nodeLong = (Integer) attr;
				}
				attr = nodeMember.get(NODE_AREA);
				if (attr != null) nodeArea = (String) attr;

				if (nodeCode != null && controlParam != null && controlParam.trim().equals(DELETE_OPT))
				{
					preparedStmt1 = dbConnectionPool.getPreparedStmt(conn, nodesDelete);
					preparedStmt1.setString(1, nodeCode);
					preparedStmt1.executeUpdate();
					preparedStmt1.close();
					preparedStmt1 = null;

				}
				else if (nodeCode!=null && nodeArea!=null)
				{
					String correctedNodeArea = nodeArea.replaceAll(";", ",");
					StringTokenizer st = new StringTokenizer(nodeArea, ";");
					ArrayList longLatList = new ArrayList();
					while(st.hasMoreTokens())
					{
						longLatList.add(st.nextToken().trim());
					}
					if (!longLatList.get(0).equals(longLatList.get(longLatList.size()-1)))
					{
						correctedNodeArea = correctedNodeArea + "," + longLatList.get(0);
					}
					preparedStmt2 = dbConnectionPool.getPreparedStmt(conn, nodesQuery);
					preparedStmt2.setString(1, nodeCode);
					rs1 = preparedStmt2.executeQuery();
					if (rs1.next() == false) //if nodes table entry is to be inserted
					{
						preparedStmt3 = dbConnectionPool.getPreparedStmt(conn, nodesInsertFull);
						preparedStmt3.setString(1, nodeCode);
						preparedStmt3.setDouble(2, nodeLat);
						preparedStmt3.setDouble(3, nodeLong);
						preparedStmt3.setString(4, "Polygon(("+correctedNodeArea+"))");
						preparedStmt3.executeUpdate();
						preparedStmt3.close();
						preparedStmt3 = null;
						
						String[] languages = config.getLanguages();
						for (int j=0; j< languages.length; j++)
						{
							Object nodeName = nodeMember.get(NODE_NAME_ + languages[j].toUpperCase());
							if (nodeName != null)
							{
								preparedStmt4 = dbConnectionPool.getPreparedStmt(conn, nodeNamesInsert);
								preparedStmt4.setString(1, nodeCode);
								preparedStmt4.setString(2, languages[j]);
								preparedStmt4.setBytes(3, ((String)nodeName).getBytes("UTF-8"));
								preparedStmt4.executeUpdate();
								preparedStmt4.close();
								preparedStmt4 = null;
							}
						}
					}
					else //if nodes table entry is to be updated
					{
						preparedStmt5 = dbConnectionPool.getPreparedStmt(conn, nodesUpdate);
						preparedStmt5.setDouble(1, nodeLat);
						preparedStmt5.setDouble(2, nodeLong);
						preparedStmt5.setString(3, "Polygon(("+correctedNodeArea+"))");
						preparedStmt5.setString(4, nodeCode);
						preparedStmt5.executeUpdate();
						preparedStmt5.close();
						preparedStmt5 = null;
						String[] languages = config.getLanguages();
						for (int j=0; j< languages.length; j++)
						{
							Object nodeName = nodeMember.get(NODE_NAME_ + languages[j].toUpperCase());
							if (nodeName != null)
							{
								preparedStmt5A = dbConnectionPool.getPreparedStmt(conn, nodeNamesQuery);
								preparedStmt5A.setString(1, nodeCode);
								preparedStmt5A.setString(2, languages[j]);
								rs2 = preparedStmt5A.executeQuery();
								if (rs2.next() == true)
								{
									preparedStmt6 = dbConnectionPool.getPreparedStmt(conn, nodeNamesUpdate);
									preparedStmt6.setBytes(1, ((String)nodeName).getBytes("UTF-8"));
									preparedStmt6.setString(2, nodeCode);
									preparedStmt6.setString(3, languages[j]);
									preparedStmt6.executeUpdate();
									preparedStmt6.close();
									preparedStmt6 = null;
								}
								else
								{
									preparedStmt6A = dbConnectionPool.getPreparedStmt(conn, nodeNamesInsert);
									preparedStmt6A.setString(1, nodeCode);
									preparedStmt6A.setString(2, languages[j]);
									preparedStmt6A.setBytes(3, ((String)nodeName).getBytes("UTF-8"));
									preparedStmt6A.executeUpdate();
									preparedStmt6A.close();
									preparedStmt6A = null;
								}
								rs2.close();
								rs2 = null;
								preparedStmt5A.close();
								preparedStmt5A = null;
							}
						}
					}
					rs1.close();
					rs1 = null;
					preparedStmt2.close();
					preparedStmt2 = null;
				}
				else
				{
					throw new Exception("Invalid Node Info List details in feed. "+ "nodeCode "+ nodeCode +  " nodeArea " + nodeArea);
				}
			} // end of 'for (int i=0; i<nodeList.size(); i++)'
				
			conn.commit();
		}
 		catch (Exception e) 
 		{
 			System.out.println("Failed to update nodeInfo in Database: "+ e);
 			e.printStackTrace();
 		}
 		finally
 		{
    		if (rs1 != null)
    		{
    			try {rs1.close();} catch (SQLException e) {} 
    			rs1 = null;
    		}
    		if (rs2 != null)
    		{
    			try {rs2.close();} catch (SQLException e) {} 
    			rs2 = null;
    		}
    		if (preparedStmt1 != null)
    		{
    			try {preparedStmt1.close();} catch (SQLException e) {} 
    			preparedStmt1 = null;
    		}
    		if (preparedStmt2 != null)
    		{
    			try {preparedStmt2.close();} catch (SQLException e) {} 
    			preparedStmt2 = null;
    		}
    		if (preparedStmt3 != null)
    		{
    			try {preparedStmt3.close();} catch (SQLException e) {} 
    			preparedStmt3 = null;
    		}
    		if (preparedStmt4 != null)
    		{
    			try {preparedStmt4.close();} catch (SQLException e) {} 
    			preparedStmt4 = null;
    		}
    		if (preparedStmt5 != null)
    		{
    			try {preparedStmt5.close();} catch (SQLException e) {} 
    			preparedStmt5 = null;
    		}
    		if (preparedStmt5A != null)
    		{
    			try {preparedStmt5A.close();} catch (SQLException e) {} 
    			preparedStmt5A = null;
    		}
    		if (preparedStmt6 != null)
    		{
    			try {preparedStmt6.close();} catch (SQLException e) {} 
    			preparedStmt6 = null;
    		}
    		if (preparedStmt6A != null)
    		{
    			try {preparedStmt6A.close();} catch (SQLException e) {} 
    			preparedStmt6A = null;
    		}
			if (conn != null)
			{
				try {conn.close();} catch (SQLException e) {}
				conn = null;
			}
 		}
	}
}
